3.07. Работа с СУБД

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВНЕ ДЛЯ НОВИЧКОВВ РАЗРАБОТКЕ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Работа с СУБД

★ СУБД – система управления базами данных, программный комплекс, включающий не просто набор языковых инструментов, но и средства для администрирования.

Современные СУБД опираются на набор взаимосвязанных объектов, которые образуют структурированную среду для хранения и обработки информации. Таблицы выступают центральными элементами, хранящими данные в строках и столбцах, где каждое поле обладает определённым типом данных, задающим характер хранимой информации и допустимые операции. Для ускорения доступа к данным создаются индексы – специальные структуры, оптимизирующие поиск подобно оглавлению в книге. Процедуры и функции инкапсулируют бизнес-логику, позволяя выполнять сложные операции непосредственно на сервере. Система безопасности реализуется через пользователей и права доступа, где каждому субъекту назначаются конкретные привилегии на определённые объекты.

Эти компоненты образуют иерархическую систему: от простых типов данных к таблицам, связанным отношениями, от индексов, ускоряющиз запросы, до процедур, автоматизирующих обработку, и пользовательских ролей, обеспечивающих безопасность. Такая организация позволяет СУБД эффективно балансировать между гибкостью (благодаря настраиваемым структурам) и производительностью (за счёт оптимизированных механизмов хранения и доступа), удовлетворяя требованиям как небольших приложений, так и корпоративных систем.

Ключевая особенность – декларативность: разработчик описывает что нужно (структуру данных, правила доступа), а СУБД решает, как это оптимально реализовать. Но это мы забегаем к SQL. Начнём с NoSQL.

Пользователи – это учётные записи для доступа к БД. Они не всегда люди, часто под пользователем может быть программа (в коде программы будет авторизация).

Кто использует СУБД? Программисты, обычные пользователи и администраторы. Конечно, ролевая модель может быть шире, но общий принцип такой:

• программисты пишут программы, работающие с БД, создают таблицы, изменяют данные, либо только читают и обновляют их; -обычные пользователи пользуются программой и часто не пользуются БД, а если и пользуются, то исключительно для чтения, без права изменения и удаления;
• администраторы же создают и настраивают БД, и предоставляют права доступа.

В SQL, для работы с БД используются команды управления доступом.

Работа с правами

1. Создание пользователей:

-- Создаём нового пользователя
CREATE USER 'менеджер'@'localhost' IDENTIFIED BY 'password123';

С созданием всё просто – это команда, используемая для добавления нового пользователя с указанным именем и паролем.

2. Выдача прав (GRANT):

-- Разрешаем чтение таблицы товаров
GRANT SELECT ON магазин.товары TO 'менеджер'@'localhost';

-- Разрешаем добавление новых заказов
GRANT INSERT ON магазин.заказы TO 'менеджер'@'localhost';

Выдача прав подразумевает расширение перечня прав пользователя и предоставления доступа к объекту. В данном случае к заказам и товарам.

3. Запрет прав (REVOKE):

-- Забираем право удалять товары
REVOKE DELETE ON магазин.товары FROM 'менеджер'@'localhost';

Если у кого-то слишком много прав – можно их отозвать. В данном случае выполняется отзыв прав на товары.

4. Просмотр выданных прав:

-- Какие права у пользователя?
SHOW GRANTS FOR 'менеджер'@'localhost';

Права в СУБД – разрешения на действия, именуются также привилегиями.

Какие бывают права в SQL?

Право	Дозволенные действия	Пример
SELECT	Читать данные	Просмотр каталога товаров
INSERT	Добавлять новые записи	Оформление нового заказа
UPDATE	Изменять существующие данные	Обновление цены товара
DELETE	Удалять данные	Удаление отменённого заказа
ALL	Все возможные права	Только для администраторов
CREATE	Создавать новые таблицы	Разработчикам для создания объектов
EXECUTE	Запуск хранимых процедур	Вызов процедуры формирования отчёта
ALTER	Изменение структуры объектов	Изменение таблиц (столбцов)
DROP	Удалять объекты	Удаление таблицы целиком
GRANT	Назначение прав другим	Дать права на удаление кому-то

При администрировании СУБД важно придерживаться принципа минимальных прав – давать только необходимые разрешения, не давать права админа, регулярно проверять права и использовать сложные пароли для всех пользователей.

Данные – это серьёзно. Если программу можно написать заново, то данные не восстановить, если нет резервных копий.

Виды СУБД

Рассмотрим основные реляционные СУБД, их особенности, инструменты для работы и ключевые отличия в синтаксисе.

SQLite

★ SQLite – встраиваемая СУБД (не требует сервера). Вся база данных хранится в одном файле (формат .db или .sqlite), благодаря чему можно просто обмениваться базой, переносить её и хранить где угодно (потому и прозвали «лайт»). Однако у неё нет сетевого доступа, только локальное использование. Но она достаточно мощная, несмотря на минималистичность.

Для работы с SQLite используются клиенты:

• DB Browser for SQLite (графический интерфейс);
• SQLite CLI (командная строка);
• DBeaver;
• Расширения для VS Code (SQLite).

SQLite оправдывает «лайт» в своём названии, не обладая чем-то уникальным, скорее наоборот, данная СУБД ограничена по сравнению с другими. Какие особенности у SQLite?

Вся БД - один файл. Можно просто скопировать файл .db и данные готовы к использованию на любой другой машине, можно прикрепить к письму, залить на Git, и открыть на другом устройстве. У других СУБД аналогов нет, они требуют сервера, конфигурации, дампов/восстановлений.

Поскольку нет сервера, нет конфигурации и нет администрирования, этот формат отлично подходит для новичков - не нужно париться о портах, демонах, правах доступа, пользователях и ролях. Просто подключаемся к файлу и работаем. Мобильные приложения и некоторые игры часто используют SQLite именно поэтому, ведь можно встроить файл БД прямо в приложение.

SQLite полностью встраиваемая. Это библиотека на C, которая связывается прямо с приложением, работает в том же процессе. Поддерживается в огромном количестве языков и платформ. Размер на диске маленький, работать может даже на устройствах с 1 МБ ОЗУ, поэтому можно встретить в спутниках, роутерах и медицинских устройствах.

Поскольку SQLite работает без сервера, то и ACID-транзакции здесь работают даже при сбоях питания, на уровне файловой системы. Обычные текстовые файлы, к примеру, так не смогут, поэтому данные будут в безопасности, и то, что это один файл, не страшно.

Но всё же, есть минусы такой «лёгкости». Когда речь идёт о высокой нагрузки, сложных запросах и тысячах пользователей, SQLite «захлебнётся». Здесь нет сетевого доступа, нет ролевой модели, нет хранимых процедур, триггеров, нет параллелизма, репликации, кластеризации, шардинга. Использовать для себя или узкого круга - пожалуйста, но в промышленном масштабе лучше взять «серьёзную» СУБД.

К примеру, параллелизм. В контексте СУБД это подразумевает параллельный доступ к базе данных и параллельное редактирование. То есть, одновременно несколько пользователей могут работать - в SQLite это не работает так, как ожидается - производится только одна запись в момент времени, и при этом блокируется вся БД.

Пример синтаксиса:

-- Автоинкремент
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

-- Вставка
INSERT INTO users (name) VALUES ('Нико');

-- Лимит
SELECT * FROM users LIMIT 10;

Официальная документация SQLite здесь - https://www.sqlite.org/

Практическое задание
Установите DB Browser for SQLite.

MySQL

★ MySQL – самая популярная open-source СУБД, с поддержкой репликации и кластеризации, оптимизированная для веб-приложений, поддерживает движки.

Чит-лист - https://cheatsheets.zip/mysql

и ещё один - https://learnsql.com/blog/mysql-cheat-sheet/

Клиенты:

• MySQL Workbench (официальный GUI);
• HeidiSQL (лёгкий клиент);
• DBeaver (универсальный);
• SQL Manager;
• phpMyAdmin (веб-интерфейс).

MySQL можно поставить на второе место по «лёгкости» после SQLite. Здесь требуется наличие запущенного сервера, и одним файлом не обойтись. В основном, всё что умеет MySQL, умеют и другие СУБД - Oracle, PostgreSQL, MS SQL, но всё равно есть и уникальные вещи.

К примеру, только в MySQL можно выбирать движок для каждой таблицы (поддержка смены движков хранения на уровне таблиц). В одной базе можно создать таблицы на разных движках, например, одну на InnoDB, другую на MyISAM, третью на Memory, Archive, CSV и т.д.

Движок хранения - это модуль СУБД, который отвечает за то, как и где хранятся данные (в памяти/на диске), как обрабатываются запросы, как работают транзакции, блокировки, индексы, внешние ключи, восстановление, и конечно за производительность чтения/записи. Словом, это основной «исполнитель».

InnoDB - основной и рекомендуемый, поддерживает ACID-транзакции, внешние ключи, блокировки, краш-устойчивость, многоверсионность (дла параллельных чтений).

MyISAM уже устаревший, не поддерживает транзакции, внешние ключи, блокировки на уровне строк, надежное восстановление, но очень быстрый для SELECT, INSERT.

MEMORY (другое название HEAP) - хранение в оперативной памяти, из-за этого очень быстрый, так как данные не пишутся на диск. При перезагрузке сервера данные исчезают, транзакций нет, и таблицы пересоздаются при старте.

CSV - хранение в виде CSV-файлов, которые можно редактировать через Excel. Для импорта может быть очень удобным, но не будет индексов, транзакций, и работа довольно медленная.

ARCHIVE - для архивных и редко читаемых данных, предполагает сильное сжатие данных, чтобы хранить логи, историю, архивы. Здесь только INSERT и SELECT, индексов нет, а SELECT очень медленный.

BLACKHOLE - «проглатывает» всё, то есть принимает данные, но ничего не хранит, буквально с перевода «чёрная дыра», подходит для тестирования или репликации. Такой вот «фейковый» движок для специфичных сценариев.

FEDERATED/FEDERATEDX предоставляет доступ к таблицам на удалённом сервере. Таблица в локальной БД ссылается на таблицу на другом MySQL-сервере, как «внешняя таблица». Медленный, устаревший движок.

MERGE тоже устарел, и работает только с MyISAM. Можно делать «виртуальную» таблицу, которая объединит несколько таблиц с одинаковой структурой, допустим, для партиционирования.

В других СУБД нет подобной системы движков хранения. Везде один движок. Поэтому такая особенность и все эти движки характерны именно для MySQL.

В MySQL имеются очень быстрые операции INSERT и SELECT, если правильно настроить. Исторически данная СУБД оптимизирована под высокую пропускную способность чтения и записи, что и сделало её лучшим выбором для веб-сайтов.

MySQL одной из первых предложила простую и надёжную репликацию, с необычным способом разделения на MASTER и SLAVE (хозяин и раб, дословным переводом). Пример:

CHANGE MASTER TO MASTER_HOST='...', MASTER_USER='...', MASTER_PASSWORD='...';
START SLAVE;

MySQL также позволяет выдавать права не только на таблицы, но и на отдельные столбцы. В других СУБД этого нельзя делать напрямую. Можно, к примеру, ограничить видимость, чтобы пользователь не видел столбец «Phone».

Имеется также пара функций, которые уникальны для этой СУБД:

• ON DUPLICATE KEY UPDATE - обновление только если запись существует;
• REPLACE INTO - если запись с таким первичным ключом или UNIQUE существует, то удаляет и вставляет новую.

Если говорить об ограничениях MySQL, то они были в основном до версии 8.0. Раньше в MySQL не было полноценных оконных функций, полной поддержки CTE (WITH), встроенной поддержки JSON-индексов и операций.

Можно сказать, что MySQL - классическая РСУБД (реляционная СУБД), без каких-то особых «эксклюзивных» возможностей.

Пример синтаксиса:

-- Автоинкремент
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- Вставка с возвратом ID
INSERT INTO users (name) VALUES ('Гордон');
SELECT LAST_INSERT_ID();

-- Лимит
SELECT * FROM users LIMIT 10 OFFSET 5;

Из полезных ресурсов по MySQL можно отметить два:

• официальная документация - https://dev.mysql.com/doc/
• форум сообщества на русском языке - http://www.mysql.ru/docs

Microsoft SQL Server

★ Microsoft SQL Server – проприетарная СУБД от Microsoft, интегрированная с Windows и .NET, поддерживает специфический язык T-SQL (расширенный SQL), обладает мощными инструментами аналитики (SSAS, SSRS).

Чит-лист - https://learnsql.com/blog/sql-server-cheat-sheet/

Клиенты:

• SQL Server Management Studio (SSMS) – основной GUI;
• Azure Data Studio (кросс-платформенный клиент);
• SQL Manager;
• DBeaver.

SQL-сервер является очень мощной СУБД. Здесь используется сервер, и для управления, администрирования и конфигурирования сервера есть стандартная утилита SQL Server Management Studio (SSMS).

Главная особенность MS SQL - T-SQL, полноценный процедурный язык. Можно отметить следующие уникальные операции и конструкции: MERGE - команда «всё в одном», UPDATE+INSERT+DELETE.

Пример:

MERGE TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID
WHEN MATCHED THEN
    UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
    INSERT (ID, Name) VALUES (source.ID, source.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

OUTPUT - возврат изменённых строк в том же запросе. Показывает, какие строки были удалены. Пример:

DELETE FROM Orders
OUTPUT deleted.OrderID, deleted.CustomerID
WHERE Status = 'Cancelled';

FILESTREAM позволяет хранить большие файлы (BLOB) не в базе, а в файловой системе, и управлять ими через SQL-запросы. Файлы хранятся на диске, но доступны через SELECT/UPDATE.

FileTable позволяет открывать сетевую папку через Windows Explorer и копировать файлы прямо туда, а SQL Server будет автоматически создавать записи в таблице:

CREATE TABLE MyFiles AS FileTable;

В MS SQL есть интегрированные BI-инструменты для аналитики:

• SSIS (SQL Server Integration Services) — ETL-платформа, визуальный конструктор пакетов переноса/очистки/преобразования данных. Поддерживает сотни источников (Excel, Oracle, CSV, API и т.д.).
• SSAS (SQL Server Analysis Services) — OLAP и семантические модели. Включает в себя многомерные кубы и табличные модели, интегрирован с Power BI, Excel. Имеет особый уникальный язык DAX (Data Analysis Expressions).
• SSRS (SQL Server Reporting Services) для отчётности. Визуализирует, позволяет экспортировать и даже использовать встроенный веб-портал. Ни одна другая СУБД не предоставляет такой комплексной, «коробочной» BI-платформы.

Поскольку Microsoft курирует и .NET платформу, то имеется тесная интеграция с ней. Например, имеется SQLCLR — выполнение .NET-кода внутри SQL Server. Можно писать функции, триггеры, агрегаты на C#/VB.NET и вызывать их из SQL. Это уникально, ни одна другая СУБД так не может. Сейчас SQLCLR отключён по умолчанию (из соображений безопасности), но возможность остаётся.

Пример синтаксиса (T-SQL):

-- Автоинкремент
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50)
);

-- Вставка с возвратом ID
INSERT INTO users (name) VALUES ('Клауд');
SELECT SCOPE_IDENTITY();

-- Лимит (с 2012 года)
SELECT * FROM users ORDER BY id OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

Документация доступна у Microsoft - https://learn.microsoft.com/ru-ru/sql/

Практическое задание
Установите SQL Server Express
Установите SSMS (SQL Server Management Studio)
Подключитесь к локальному серверу.

PostgreSQL

★ PostgreSQL – самая продвинутая open-source СУБД (хотя есть версия Pro), поддержка JSON, GIS, полнотекстового поиска. Расширяемая (можно писать функции на Python, LO/pgSQL), имеет строгую поддержку стандартов SQL.

Чит-лист - https://cheatsheets.zip/postgres

и ещё один - https://learnsql.com/blog/postgresql-cheat-sheet/

Клиенты:

• pgAdmin (официальный GUI);
• DBeaver;
• SQL Manager;
• Postico (macOS).

Основной процедурный язык PostgreSQL - PL/pgSQL. Можно сказать, что это аналог T-SQL. Существуют расширения, вроде PL/Python, PL/Perl, PL/Java, PL/V8(JS), позволяющие писать функции на соответствующих языках программирование.

Расширений в PostgreSQL много. Они являются расширением функционала:

• postgis - поддержка геоинформационных данных (GIS) для карт, маршрутов, геозон;
• pg_trgm - триграммный поиск, для нечёткого сравнения;
• hstore - хранение пар «ключ-значение»;
• uuid-ossp - генерация UUID (уникальных идентификаторов);
• pg_cron - запуск задач по расписанию внутри PostgreSQL;
• timescaledb - оптимизация для временных рядов;
• pgvector - хранение и поиск векторов;
• pg_partman - автоматическое партиционирование таблиц.

Другие СУБД требуют установки отдельных продуктов или вовсе не поддерживают такие «плагины».

Особенно важно выделить PostGIS, имеющую лучшую поддержку геоданных среди всех СУБД. Позволяет хранить точки, линии, полигоны и выполнять пространственные запросы, поддерживает CRS (системы координат), геоиндексы, функции, и всё это делает де-факто стандартом в ГИС-индустрии. В PostgreSQL имеется качественный полнотекстовый поиск, с поддержкой различных возможностей - стемминг (приведение слов к основе), стоп-слов, ранжирования, фраз, весов полей, словарей. В других СУБД такой функционал сильно слабее.

Важная особенность PostgreSQL - материализованные представления. Мы их будем изучать в разделе, посвящённом представлениям, просто сейчас запомним что они уникальны. В MySQL такого нет, в SQL Server называются иначе и имеют ограничения (Indexed Views), в Oracle намного сложнее в использовании.

Пример синтаксиса:

-- Автоинкремент
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- Вставка с возвратом ID
INSERT INTO users (name) VALUES ('Кратос') RETURNING id;

-- Лимит
SELECT * FROM users LIMIT 10 OFFSET 5;

Погрузиться в PostgreSQL можно:

• на официальном сайте - https://www.postgresql.org/;
• на сайте Postgres Professional - https://postgrespro.ru/docs

Oracle

★ Oracle Database – проприетарная СУБД для корпоративного использования, с высокой производительностью и масштабируемостью, поддержкой PL/SQL, однако имеет непростую лицензионную политику.

Клиенты:

• Oracle SQL Developer (официальный GUI);
• TOAD for Oracle;
• SQL Manager;
• DBeaver.

Уникальная возможность Oracle DB - масштабирование «по горизонтали», Real Application Clusters (RAC). При этом несколько серверов (узлов) работают с одной и той же БД одновременно, и если один упадёт, другие продолжают работать. К примеру, если есть 4 сервера, и все подключены к общему хранилищу, при падении одного, клиенты даже не заметят. В PostgreSQL нет нативного RAC, в SQL Server и MySQL имеются ограничения, поэтому сейчас в Oracle единственный полноценный мульти-кластер для таких нагрузок на уровне ядра СУБД.

В Oracle DB есть возможность Flashback Technology, при помощи которой можно вернуть базу (или её отдельные таблицы/строки) в определённое состояние, например «на вчера в 15:00», без бэкапов и остановки.

Пример:

FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('2025-04-01 14:00:00');

Или отмена DROP:

FLASHBACK TABLE deleted_table TO BEFORE DROP;

Другие СУБД не позволяют так легко и быстро откатываться.

Также здесь имеется встроенное машинное обучение (Oracle Data Mining), продвинутая аналитика, что позволяет строить и применять модели ML прямо в SQL без выгрузки данных.

Здесь тоже есть материализованные представления с автоматическим обновлением и использованием вместо исходных таблиц для ускорения запросов. В PostgreSQL они обновляются вручную, через REFRESH MATERIALIZED VIEW. Здесь же можно к примеру выполнить REFRESH FAST (только измененные строки), ON COMMIT (при каждом коммите):

CREATE MATERIALIZED VIEW sales_summary
REFRESH FAST ON COMMIT
AS
SELECT product_id, SUM(amount) AS total
FROM sales GROUP BY product_id;

PL/SQL здесь работает в чистом виде. Главное не путать:

• SQL Server использует T-SQL;
• PostgreSQL использует PL/pgSQL;
• Oracle DB использует PL/SQL.

Можно сказать, что PL/SQL это полноценная среда разработки внутри базы, с пакетами, коллекциями, исключениями, триггерами, объектами и типами. Пакеты похожи на классы, коллекции используются для массивов и вложенных таблиц. Компиляция производится нативно (средствами базовой СУБД), и довольно быстрая.

Имеется здесь также In-Memory Column Store, хранение данных одновременно в строковом и колоночном формате, для оптимизации работы аналитических запросов. Для приложения ничего не меняется, работает «магически».

Пример синтаксиса (PL/SQL):

-- Автоинкремент через SEQUENCE
CREATE SEQUENCE user_seq;
CREATE TABLE users (
    id NUMBER DEFAULT user_seq.NEXTVAL PRIMARY KEY,
    name VARCHAR2(50)
);

-- Вставка с возвратом ID
INSERT INTO users (name) VALUES ('Джилл') RETURNING id INTO :new_id;

-- Лимит (через ROWNUM)
SELECT * FROM users WHERE ROWNUM <= 10;

Прочие СУБД SQL

СУБД	Особенности	Клиенты
MariaDB	Форк MySQL с улучшениями	MySQL Workbench, HeidiSQL
Firebird	Компактная, для встраиваемых систем	FlameRobin, DBeaver
DB2	Корпоративная СУБД от IBM	IBM Data Studio, DBeaver

Однако, мы не затронули одну важную СУБД - ClickHouse. Давайте рассмотрим её отдельно.

ClickHouse и колоночная архитектура

ClickHouse - это и не NoSQL, и не SQL. Но всё же ближе к SQL.

★ ClickHouse - высокопроизводительная колоночная (column-oriented) СУБД с открытым исходным кодом. Используется для обработки аналитических запросов в режиме реального времени. Разработчиком является «Яндекс» - изначально как эксперимент для Яндекс Метрика. Но затем спрос на функционал вырос и она стала открытой для общественности под лицензией Apache 2. Она подходит для работы с большими объёмами данных и сложными аналитическими запросами - бизнес-аналитика, логирование, мониторинг и обработка событий.

ClickHouse не является NoSQL-базой данных в классическом понимании. Она поддерживает SQL-подобный язык запросов, таблицы, индексы и другие элементы, характерные для реляционных баз данных. Поэтому ClickHouse можно назвать гибридом.

В отличие от традиционных строковых БД, ClickHouse имеет колоночную архитектуру, и хранит данные по столбцам. Это позволяет эффективно читать только те столбцы, которые нужны для выполнения запроса, что уменьшает объем обрабатываемых данных. Колоночное хранение также способствует лучшему сжатию данных за счёт их однородности.

ClickHouse очень оптимизирован и может обрабатывать миллиарды строк за секунды, благодаря параллельной обработке данных и эффективному использованию аппаратных ресурсов. Однако ClickHouse не предназначен для операций с частыми обновлениями данных вроде UPDATE или DELETE, он лучше работает со вставкой новых данных.

В ClickHouse используется SQL-подобный язык запросов. Он очень похож и поддерживает большинство операций, но имеется ряд особенностей, которые сближают с NoSQL-системами:

• колоночная архитектура;
• ограниченная поддержка транзакций (ACID не поддерживается в полном объеме), операции UPDATE и DETELE выполняются медленно и не рекомендуются;
• поддержка распределенных таблиц и шардирования, что типично для NoSQL - можно масштабировать систему на сотни серверов, что недоступно для SQL БД;
• отсутствие строгой схемы данных , можно добавлять данные без строгой проверки типов.

ClickHouse поэтому не так легко встретить в списках популярных СУБД. ClickHouse называют аналитической СУБД или OLAP-системой (On-Line Analytical Processing) — это промежуточное положение между SQL и NoSQL.

Такая система актуальна для дата-инженеров (обработка данных), аналитиков данных, бизнес-аналитиков и DevOps-инженеров (мониторинг). Давайте остановимся на формате хранения в виде столбцов.

Что такое колоночная архитектура?

Как мы рассмотрели ранее, обычные реляционные базы данных (MySQL или PostgreSQL, например) хранят данные в строках. Это означает, что все значения одной строки записываются в блоке подряд. Например:

ID	Name	Age	City
1	Айнур	20	Уфа
2	Ильнур	25	Стерлитамак
3	Фанур	30	Сибай

В строковой архитектуре эти данные хранятся так:

[1, "Айнур", 20, "Уфа"], [2, "Ильнур", 25, "Стерлитамак"], [3, "Фанур", 30, "Сибай"]

И если мы захотим прочитать аналитическим запросом, к примеру:

SELECT AVG(Age) FROM users;

То база данных будет читать все строки целиком, хотя нас интересует столбец Age.

Колоночная архитектура хранит данные по столбцам, а не строкам, и каждый столбец хранится отдельно. В нашем случае:

ID : [1, 2, 3]
Name : ["Айнур", "Ильнур", "Фанур"]
Age : [25, 30, 22]
City : ["Уфа", "Стерлитамак", "Сибай"]

И теперь, если мы выполним тот же SQL-запрос, ClickHouse прочитает только столбец Age, игнорируя остальные данные. Это значительно уменьшает объём чтения и ускоряет выполнение запроса.

В типичном аналитическом запросе используются только несколько столбцов из всей таблицы. И если в строковой архитектуре пришлось бы читать все строки целиком, включая ненужные столбцы, то в колоночной читается только нужный столбец — это значительно уменьшает нагрузку на диск и память.

Кроме этого, колоночная архитектура позволяет распараллелить операции. Например, если нужно посчитать сумму значений в столбце Age, каждую часть столбца можно обработать на разных процессорах или серверах.

Какие есть недостатоки у колоночной архитектуры?

1. Медленная запись данных. Вставка новых строк требует обновления всех столбцов, что значительно медленнее чтения.
2. Сложность обновления данных. Изменение одной строки затрагивает несколько столбцов, что усложняет операции UPDATE и DELETE.
3. Не подходит для OLTP (On-Line Transaction Processing). Если нужно часто читать и изменять отдельные строки, строковая архитектура будет эффективнее.

Именно из-за процесса изменения данных зачастую везде и выбирают строковую архитектуру, а колоночная используется именно в анализе.

Подробности о ClickHouse можно получить здесь - https://clickhouse.com/docs